﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using seejee_Backendium.Core.Interfaces;
using seejee_Backendium.Core.Models;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Threading.Tasks;

namespace seejee_Backendium.Data.Services
{
    // ChartDataService.cs
    // ChartDataService.cs
    public class ChartDataService : IChartDataService
    {
        private readonly string _connectionString;
        private readonly ILogger<ChartDataService> _logger;

        public ChartDataService(IConfiguration config, ILogger<ChartDataService> logger)
        {
            _connectionString = config.GetConnectionString("Default");
            _logger = logger;
        }

        public async Task<DashboardConfigDto> GetDashboardConfigAsync(string Name)
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync(); // 显式打开连接

            try
            {
                // 1. 获取主表信息
                var mainTable = await connection.QueryFirstOrDefaultAsync<SEBI_BIMainTable>(
                    "SELECT * FROM SEBI_BIMainTable WHERE Name = @Name",
                    new { Name = Name });

                if (mainTable == null) return null;

                // 2. 获取所有容器
                var containers = await connection.QueryAsync<SEBI_Container>(
                    "SELECT * FROM SEBI_Container WHERE BIMainTableId = @Id ORDER BY Sort",
                    new { Id = mainTable.Id });

                // 3. 构建返回对象
                var dashboardConfig = new DashboardConfigDto
                {
                    BIMainTableId = mainTable.ContainerId,
                    Name = mainTable.Name,
                    CreatedAt = mainTable.CreatedAt,
                    BackgroundColor = mainTable.BackgroundColor,
                    Items = new List<DashboardContainerDto>()
                };
                var visited = new HashSet<int>();
                // 4. 处理每个容器
                foreach (var container in containers)
                {
                    var containerDto = await BuildContainerDtoAsync(connection, container, visited);
                    dashboardConfig.Items.Add(containerDto);
                }

                return dashboardConfig;
            }
            catch (SqlException sqlEx)
            {
                // 数据库相关异常处理
                _logger.LogError(sqlEx, "数据库操作异常");
                throw; // 重新抛出异常
            }
            catch (Exception ex)
            {
                // 其他未预料异常处理
                _logger.LogError(ex, "获取仪表板配置时发生未知错误");
                throw; // 重新抛出异常
            }
            finally
            {
                await connection.CloseAsync();
            }
        }

        private async Task<DashboardContainerDto> BuildContainerDtoAsync(
            SqlConnection connection,
            SEBI_Container container,
            HashSet<int> visited
            )
        {
            var containerDto = new DashboardContainerDto
            {
                Id = container.ContainerId,
                Type = container.Type,
                Name = container.Name,
                Span = container.Span,
                Height = container.Height,
                Title = container.Title,
                BackgroundColor = container.BackgroundColor,
                HeaderColor = container.HeaderColor,
                TitleColor = container.TitleColor,
                TitleSize = container.TitleSize,
                DividerColor = container.DividerColor,
                DividerGradient = container.DividerGradient,
                DividerHeight = container.DividerHeight,
                Sort = container.Sort
            };

            // 获取容器内的所有子项
            await AddContainerItemsAsync(connection, container.Id, containerDto.children, visited);

            return containerDto;
        }

        private async Task AddContainerItemsAsync(
            SqlConnection connection,
            int containerId,
            List<DashboardItemDto> items,
             HashSet<int>? visited = null)
        {
            visited ??= new HashSet<int>();
            if (!visited.Add(containerId))
            {
                // 已经访问过，说明有环，直接返回，防止死循环
                _logger.LogWarning("检测到容器递归环路，containerId={ContainerId}", containerId);
                return;
            }
            // 并行获取所有类型的子项
            var textItemsTask = GetTextItemsAsync(connection, containerId);
            var tableItemsTask = GetTableItemsAsync(connection, containerId);
            var ringItemsTask = GetRingItemsAsync(connection, containerId);
            var pieItemsTask = GetPieItemsAsync(connection, containerId);
            var lineItemsTask = GetLineItemsAsync(connection, containerId);
            var barItemsTask = GetBarItemsAsync(connection, containerId);
            var flipNumberItemsTask = GetFlipNumberItemsAsync(connection, containerId);
            var nestedContainersTask = GetNestedContainersAsync(connection, containerId, visited);

            await Task.WhenAll(
                textItemsTask, tableItemsTask, ringItemsTask, pieItemsTask,
                lineItemsTask, barItemsTask, flipNumberItemsTask, nestedContainersTask);

            // 合并所有子项并按Sort排序
            var allItems = new List<DashboardItemDto>();
            allItems.AddRange(textItemsTask.Result);
            allItems.AddRange(tableItemsTask.Result);
            allItems.AddRange(ringItemsTask.Result);
            allItems.AddRange(pieItemsTask.Result);
            allItems.AddRange(lineItemsTask.Result);
            allItems.AddRange(barItemsTask.Result);
            allItems.AddRange(flipNumberItemsTask.Result);
            allItems.AddRange(nestedContainersTask.Result);

            items.AddRange(allItems.OrderBy(x => x.Sort));
        }

        #region 各种子项的获取方法

        private async Task<List<DashboardItemDto>> GetTextItemsAsync(SqlConnection connection, int containerId)
        {
            var items = await connection.QueryAsync<SEBI_TextConfig>(
                "SELECT * FROM SEBI_TextConfig WHERE ContainerId = @ContainerId ORDER BY Sort",
                new { ContainerId = containerId });

            return items.Select(item => new DashboardItemDto
            {
                Id = item.cId,
                Type = item.Type,
                Span = item.Span,
                Height = item.Height,
                Title = item.TitleName,
                TitleColor = item.TitleColor,
                TitleSize = item.TitleSize,
                BackgroundColor = item.BackgroundColor,
                BackgroundStyle = item.BackgroundStyle,
                Content = item.Content,
                TextAlign = item.TextAlign,
                FontSize = item.FontSize,
                FontWeight = item.FontWeight,
                FontColor = item.FontColor,
                LineHeight = item.LineHeight,
                SqlList = DeserializeJson<List<string>>(item.SqlList),
                Sort = item.Sort
            }).ToList();
        }

        private async Task<List<DashboardItemDto>> GetTableItemsAsync(SqlConnection connection, int containerId)
        {
            var items = await connection.QueryAsync<SEBI_TableConfig>(
                "SELECT * FROM SEBI_TableConfig WHERE ContainerId = @ContainerId ORDER BY Sort",
                new { ContainerId = containerId });

            return items.Select(item => new DashboardItemDto
            {
                Id = item.cId,
                Type = item.Type,
                Span = item.Span,
                Height = item.Height,
                Title = item.TitleName,
                TitleColor = item.TitleColor,
                TitleSize = item.TitleSize,
                TableHeight = item.TableHeight,
                ScrollSpeed = item.ScrollSpeed,
                UpdateKey = item.UpdateKey,
                Header = DeserializeJson<List<List<string>>>(item.Header),
                TableData = DeserializeJson<List<List<string>>>(item.Data),
                Index = item.Index,
                ColumnWidth = DeserializeJson<List<List<string>>>(item.ColumnWidth),
                Align = DeserializeJson<List<string>>(item.Align),
                HeaderBgColor = item.HeaderBgColor,
                oddRowBGC = item.oddRowBGC,
                evenRowBGC = item.evenRowBGC,
                visibleRows = item.visibleRows,
                indexColumnWidth = item.indexColumnWidth,
                RowBgColors = DeserializeJson<List<string>>(item.RowBgColors),
                SqlList = DeserializeJson<List<string>>(item.SqlList),
                Sort = item.Sort,
                prop = DeserializeJson<List<List<string>>>(item.prop),
            }).ToList();
        }

        private async Task<List<DashboardItemDto>> GetRingItemsAsync(SqlConnection connection, int containerId)
        {
            var items = await connection.QueryAsync<SEBI_RingConfig>(
                "SELECT * FROM SEBI_RingConfig WHERE ContainerId = @ContainerId ORDER BY Sort",
                new { ContainerId = containerId });

            return items.Select(item => new DashboardItemDto
            {
                Id = item.cId,
                Type = item.Type,
                Span = item.Span,
                Height = item.Height,
                Title = item.TitleName,
                TitleColor = item.TitleColor,
                TitleSize = item.TitleSize,
                BackgroundColor = item.BackgroundColor,
                BackgroundStyle = item.BackgroundStyle,
                PieRingData = DeserializeJson<List<Dictionary<string, object>>>(item.Data),
                Radius = DeserializeJson<string[]>(item.Radius),
                LabelShow = item.LabelShow,
                SqlList = DeserializeJson<List<string>>(item.SqlList),
                Sort = item.Sort
            }).ToList();
        }

        private async Task<List<DashboardItemDto>> GetPieItemsAsync(SqlConnection connection, int containerId)
        {
            var items = await connection.QueryAsync<SEBI_PieConfig>(
                "SELECT * FROM SEBI_PieConfig WHERE ContainerId = @ContainerId ORDER BY Sort",
                new { ContainerId = containerId });

            return items.Select(item => new DashboardItemDto
            {
                Id = item.cId,
                Type = item.Type,
                Span = item.Span,
                Height = item.Height,
                Title = item.TitleName,
                TitleColor = item.TitleColor,
                TitleSize = item.TitleSize,
                BackgroundColor = item.BackgroundColor,
                BackgroundStyle = item.BackgroundStyle,
                PieRingData = DeserializeJson<List<Dictionary<string, object>>>(item.Data),
                Radius = DeserializeJson<string[]>(item.Radius),
                LabelShow = item.LabelShow,
                SqlList = DeserializeJson<List<string>>(item.SqlList),
                Sort = item.Sort
            }).ToList();
        }

        private async Task<List<DashboardItemDto>> GetLineItemsAsync(SqlConnection connection, int containerId)
        {
            var items = await connection.QueryAsync<SEBI_LineConfig>(
                "SELECT * FROM SEBI_LineConfig WHERE ContainerId = @ContainerId ORDER BY Sort",
                new { ContainerId = containerId });

            return items.Select(item => new DashboardItemDto
            {
                Id = item.cId,
                Type = item.Type,
                Span = item.Span,
                Height = item.Height,
                Title = item.TitleName,
                TitleColor = item.TitleColor,
                TitleSize = item.TitleSize,
                ValueColor = item.ValueColor,
                Lines = DeserializeJson<List<LineConfig>>(item.Lines),
                XAxisData = DeserializeJson<List<string>>(item.XAxisData),
                Data = DeserializeJson<List<List<double>>>(item.Data),
                BackgroundColor = item.BackgroundColor,
                BackgroundStyle = item.BackgroundStyle,
                SqlList = DeserializeJson<List<string>>(item.SqlList),
                Sort = item.Sort
            }).ToList();
        }

        private async Task<List<DashboardItemDto>> GetBarItemsAsync(SqlConnection connection, int containerId)
        {
            var items = await connection.QueryAsync<SEBI_BarConfig>(
                "SELECT * FROM SEBI_BarConfig WHERE ContainerId = @ContainerId ORDER BY Sort",
                new { ContainerId = containerId });

            return items.Select(item => new DashboardItemDto
            {
                Id = item.cId,
                Type = item.Type,
                Span = item.Span,
                Height = item.Height,
                Title = item.TitleName,
                TitleColor = item.TitleColor,
                TitleSize = item.TitleSize,
                ValueColor = item.ValueColor,
                Bars = DeserializeJson<List<BarConfig>>(item.Bars),
                XAxisData = DeserializeJson<List<string>>(item.XAxisData),
                Data = DeserializeJson<List<List<double>>>(item.Data),
                BarCategoryGap = item.BarCategoryGap,
                BarGap = item.BarGap,
                BackgroundColor = item.BackgroundColor,
                BackgroundStyle = item.BackgroundStyle,
                SqlList = DeserializeJson<List<string>>(item.SqlList),
                Sort = item.Sort
            }).ToList();
        }

        private async Task<List<DashboardItemDto>> GetFlipNumberItemsAsync(SqlConnection connection, int containerId)
        {
            var items = await connection.QueryAsync<SEBI_FlipNumberConfig>(
                "SELECT * FROM SEBI_FlipNumberConfig WHERE ContainerId = @ContainerId ORDER BY sort",
                new { ContainerId = containerId });

            return items.Select(item => new DashboardItemDto
            {
                Id = item.cId,
                Type = item.type,
                Span = item.span,
                Height = item.height,
                Title = item.titleName,
                TitleColor = item.titleColor,
                TitleSize = item.titleSize,
                ValueColor = item.valueColor,
                ValueSize = item.valueSize,
                Unit = item.unit,
                UnitSize = item.unitSize,
                IconImage = item.iconimage,
                IconPosition = item.iconPosition,
                ThousandSeparator = item.thousandSeparator,
                IconColor = item.iconColor,
                IconSize = item.iconSize,
                RingColor = item.ringColor,
                RingValue = item.ringValue,
                BackgroundColor = item.backgroundColor,
                BackgroundStyle = item.backgroundStyle,
                Leftunit = item.leftunit,
                leftunitsize = item.leftunitsize,
                layoutDirection = item.layoutDirection,
                SqlList = DeserializeJson<List<string>>(item.cSql),
                Sort = item.sort ?? 0,
                buttonshow = item.buttonshow,
                buttonname = DeserializeJson<string[]>(item.buttonname),
                buttonsql = DeserializeJson<string[]>(item.buttonsql)

            }).ToList();
        }

        private async Task<List<DashboardItemDto>> GetNestedContainersAsync(SqlConnection connection, int containerId, HashSet<int> visited)
        {
            var nestedContainers = await connection.QueryAsync<SEBI_Container>(
                "SELECT * FROM SEBI_Container WHERE Id = @ContainerId ORDER BY Sort",
                new { ContainerId = containerId });

            var containerTasks = nestedContainers.Select(container =>
                BuildContainerDtoAsync(connection, container, visited));

            return (await Task.WhenAll(containerTasks)).Cast<DashboardItemDto>().ToList();
        }

        private T DeserializeJson<T>(string json)
        {
            // 输入为 null 或空白时，直接返回默认值（避免序列化时产生 null 属性）
            if (string.IsNullOrWhiteSpace(json))
                return default;

            try
            {
                return JsonConvert.DeserializeObject<T>(json);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "JSON反序列化失败: {Json}", json);
                return default;
            }
        }

        // 数组反序列化同样优化
        private T[] DeserializeJsonArray<T>(string json)
        {
            if (string.IsNullOrWhiteSpace(json))
                return Array.Empty<T>(); // 空数组而非 null，避免序列化时出现 null

            try
            {
                return JsonConvert.DeserializeObject<T[]>(json) ?? Array.Empty<T>();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "JSON数组反序列化失败: {Json}", json);
                return Array.Empty<T>();
            }
        }

        #endregion
    }
}
